package zy.dao.stock.data.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.stock.data.DataDAO;
import zy.dto.stock.data.ProductSingleTrackDto;
import zy.dto.stock.data.StockAnalysisDto;
import zy.dto.stock.data.StockDataByDepotDto;
import zy.dto.stock.data.StockReportByShopDto;
import zy.dto.stock.data.StockShopDto;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.product.T_Base_Product_Br;
import zy.entity.base.product.T_Base_Product_Color;
import zy.entity.base.size.T_Base_Size;
import zy.entity.base.size.T_Base_SizeList;
import zy.entity.stock.data.T_Stock_Data;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.entity.stock.data.T_Stock_DataSizeView;
import zy.entity.stock.data.T_Stock_DataView;
import zy.entity.sys.user.T_Sys_User;
import zy.form.NumberForm;
import zy.util.CommonUtil;
import zy.util.StringUtil;
import zy.vo.stock.SingleTrackSQL;

@Repository
public class DataDAOImpl extends BaseDaoImpl implements DataDAO{

	@Override
	public Integer queryStockAmount(String sub_code, String dp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT IFNULL(SUM(sd_amount) ,0)");
		sql.append(" FROM t_stock_data t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_code = :sd_code ");
		sql.append(" AND sd_dp_code = :sd_dp_code ");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(),
				new MapSqlParameterSource().addValue("sd_code", sub_code).addValue("sd_dp_code", dp_code).addValue("companyid", companyid), 
						Integer.class);		
	}
	
	@Override
	public void save(List<T_Stock_DataBill> datas) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_data");
		sql.append(" (sd_pd_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_dp_code,sd_amount,sd_init,sd_date,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sd_pd_code,:sd_code,:sd_cr_code,:sd_sz_code,:sd_br_code,:sd_dp_code,:sd_amount,:sd_init,:sd_date,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(datas.toArray()));
	}

	@Override
	public void update(List<T_Stock_DataBill> datas) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_data");
		sql.append(" SET sd_amount = :sd_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_id = :sd_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(datas.toArray()));
	}
	
	@Override
	public List<String> listAllDepot(Map<String,Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT dp_code ");
		sql.append(" FROM t_base_depot dp");
		sql.append(" JOIN t_base_shop sp ON dp_shop_code = sp_code AND dp.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("JMD".equals(type)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND dp.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}

	@Override
	public Integer count(Map<String, Object> params) {
		Integer showColor = (Integer)params.get("showColor");
		Integer showSize = (Integer)params.get("showSize");
		Integer showBra = (Integer)params.get("showBra");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM(");
		sql.append(" SELECT 1");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockData(params));
//		sql.append(" AND sd_dp_code IN(");
//		sql.append(" SELECT dp_code ");
//		sql.append(" FROM t_base_depot dp");
//		sql.append(" JOIN t_base_shop sp ON dp_shop_code = sp_code AND dp.companyid = sp.companyid");
//		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
//			sql.append(getShopSQL(shop_type, 0));
//			sql.append(" WHERE 1 = 1");
//		}else{//自营、加盟、合伙
//			sql.append(" WHERE 1 = 1");
//			sql.append(" AND sp_code = :shop_code");
//		}
//		sql.append(" AND dp.companyid = :companyid)");
		sql.append(" GROUP BY sd_pd_code");
		if (showColor != null && showColor.equals(1)) {
			sql.append(",sd_cr_code");
		}
		if (showSize != null && showSize.equals(1)) {
			sql.append(",sd_sz_code");
		}
		if (showBra != null && showBra.equals(1)) {
			sql.append(",sd_br_code");
		}
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Stock_DataView> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer showColor = (Integer)params.get("showColor");
		Integer showSize = (Integer)params.get("showSize");
		Integer showBra = (Integer)params.get("showBra");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sd_id,sd_pd_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_dp_code,SUM(sd_amount) AS sd_amount,SUM(sd_init) AS sd_init,");
		sql.append(" pd_no,pd_name,pd_bd_code,pd_tp_code,pd_date,pd_year,pd_season,pd_style,pd_fabric,pd_unit,pd_cost_price,pd_sell_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sd_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sd_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sd_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockData(params));
		
//		sql.append(" AND sd_dp_code IN(");
//		sql.append(" SELECT dp_code ");
//		sql.append(" FROM t_base_depot dp");
//		sql.append(" JOIN t_base_shop sp ON dp_shop_code = sp_code AND dp.companyid = sp.companyid");
//		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
//			sql.append(getShopSQL(shop_type, 0));
//			sql.append(" WHERE 1 = 1");
//		}else{//自营、加盟、合伙
//			sql.append(" WHERE 1 = 1");
//			sql.append(" AND sp_code = :shop_code");
//		}
//		sql.append(" AND dp.companyid = :companyid)");
		
		sql.append(" GROUP BY sd_pd_code");
		if (showColor != null && showColor.equals(1)) {
			sql.append(",sd_cr_code");
		}
		if (showSize != null && showSize.equals(1)) {
			sql.append(",sd_sz_code");
		}
		if (showBra != null && showBra.equals(1)) {
			sql.append(",sd_br_code");
		}
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sd_pd_code ASC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_DataView.class));
	}

	/**
	 * 库存状况表where条件
	 */
	private String getWhereSqlOfStockData(Map<String, Object> params){
		Object depot_code = params.get("depot_code");
		Object pd_no = params.get("pd_no");
		Object pd_name = params.get("pd_name");
		Integer exactQuery = (Integer)params.get("exactQuery");
		StringBuffer sqlWhere = new StringBuffer();
		if(StringUtil.isNotEmpty(depot_code)){
			sqlWhere.append(" AND sd_dp_code = :depot_code");
		}else {
			sqlWhere.append(" AND sd_dp_code IN(:allDepot)");
		}
		if(StringUtil.isNotEmpty(pd_no)){
			if(exactQuery != null && exactQuery.equals(1)){
				sqlWhere.append(" AND pd_no = :pd_no");
			}else {
				sqlWhere.append(" AND INSTR(pd_no,:pd_no) > 0");
			}
		}
		if(StringUtil.isNotEmpty(pd_name)){
			sqlWhere.append(" AND INSTR(pd_name,:pd_name) > 0");
		}
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sqlWhere.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sqlWhere.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sqlWhere.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sqlWhere.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sqlWhere.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("showZero"))){
			if("0".equals(params.get("showZero").toString())){
				sqlWhere.append(" AND sd_amount <> 0 ");
			}
		}
		sqlWhere.append(" AND t.companyid = :companyid");
		return sqlWhere.toString();
	}
	
	@Override
	public Map<String, Object> sum(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(sd_amount) AS sd_amount,");
		sql.append(" SUM(sd_amount*pd_cost_price) AS cost_money,");
		sql.append(" SUM(sd_amount*pd_sell_price) AS sell_money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getWhereSqlOfStockData(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public List<String> list_szgcode(Map<String,Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT pd_szg_code");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockData(params));
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Map<String, Object> size_data(Map<String, Object> params){
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//条件sql
		StringBuffer sqlWhere = new StringBuffer();
		sqlWhere.append(getWhereSqlOfStockData(params));
		
		StringBuffer sql = new StringBuffer();
		//1.主数据
		sql.append("SELECT sd_id,sd_pd_code,sd_code,sd_cr_code,sd_br_code,sd_dp_code,SUM(sd_amount) AS sd_amount,pd_no,pd_name,pd_szg_code,pd_cost_price,pd_sell_price,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sd_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sd_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(sqlWhere);
		sql.append(" GROUP BY sd_pd_code,sd_cr_code,sd_br_code");
		sql.append(" ORDER BY pd_no ASC ");
		sql.append(" LIMIT :start,:end");
		List<T_Stock_DataSizeView> listMain = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_DataSizeView.class));
		//2.totalCount
		sql.setLength(0);
		sql.append("SELECT COUNT(1) FROM(");
		sql.append("SELECT sd_pd_code,sd_cr_code,sd_br_code");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(sqlWhere);
		sql.append(" GROUP BY sd_pd_code,sd_cr_code,sd_br_code)t");
		Integer totalCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class); 
		//3.查询分页尺码数量数据
		List<String> pdCodes = new ArrayList<String>();
		for (T_Stock_DataSizeView item : listMain) {
			if(!pdCodes.contains(item.getSd_pd_code())){
				pdCodes.add(item.getSd_pd_code());
			}
		}
		sql.setLength(0);
		params.put("pdCodes", pdCodes);
		List<T_Stock_Data> size_data = null;
		if(pdCodes.size()>0){
			sql.append("SELECT sd_pd_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,SUM(sd_amount) AS sd_amount");
			sql.append(" FROM t_stock_data t");
			sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
			sql.append(" WHERE 1 = 1");
			sql.append(sqlWhere);
			sql.append(" AND sd_pd_code IN(:pdCodes)");
			sql.append(" GROUP BY sd_pd_code,sd_cr_code,sd_br_code,sd_sz_code");
			size_data = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_Data.class));
			
		}
		resultMap.put("listMain", listMain);
		resultMap.put("totalCount", totalCount);
		resultMap.put("size_data", size_data);
		return resultMap;
	}
	
	/**
	 * 库存分仓表where条件
	 */
	private String getWhereSqlOfStockByDepot(Map<String, Object> params){
		Object depot_code = params.get("depot_code");
		Object pd_no = params.get("pd_no");
		Object pd_name = params.get("pd_name");
		Integer exactQuery = (Integer)params.get("exactQuery");
		StringBuffer sqlWhere = new StringBuffer();
		if(StringUtil.isNotEmpty(depot_code)){
			params.put("depot_codes", Arrays.asList(depot_code.toString().split(",")));
			sqlWhere.append(" AND sd_dp_code IN(:depot_codes)");
		}
		if(StringUtil.isNotEmpty(pd_no)){
			if(exactQuery != null && exactQuery.equals(1)){
				sqlWhere.append(" AND pd_no = :pd_no");
			}else {
				sqlWhere.append(" AND INSTR(pd_no,:pd_no) > 0");
			}
		}
		if(StringUtil.isNotEmpty(pd_name)){
			sqlWhere.append(" AND INSTR(pd_name,:pd_name) > 0");
		}
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sqlWhere.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sqlWhere.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sqlWhere.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sqlWhere.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sqlWhere.append(" AND pd_code = :pd_code ");
		}
		sqlWhere.append(" AND t.companyid = :companyid");
		return sqlWhere.toString();
	}
	
	@Override
	public List<Map<String, Object>> sum_bydepot(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT sd_dp_code,SUM(sd_amount) AS sd_amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockByDepot(params));
		sql.append(" GROUP BY sd_dp_code");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params);
	}
	
	@Override
	public Integer count_bydepot(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM(");
		sql.append(" SELECT 1");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockByDepot(params));
		sql.append(" GROUP BY sd_code)temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<StockDataByDepotDto> list_bydepot(Map<String,Object> params){
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sd_id,pd_no,pd_name,sd_pd_code,");
		sql.append(" SUM(sd_amount) AS sd_amount,GROUP_CONCAT(CONCAT(sd_dp_code,':',sd_amount)) AS depot_amount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sd_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sd_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sd_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockByDepot(params));
		sql.append(" GROUP BY sd_code");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sd_pd_code ASC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockDataByDepotDto.class));
	}
	
	
	/**
	 * 各店库存报表where条件
	 */
	private String getWhereSqlOfStockByShop(Map<String, Object> params){
		Object shopCodes = params.get("shopCodes");
		Object pd_no = params.get("pd_no");
		Object pd_name = params.get("pd_name");
		Integer exactQuery = (Integer)params.get("exactQuery");
		StringBuffer sqlWhere = new StringBuffer();
		if(StringUtil.isNotEmpty(shopCodes)){
			params.put("shop_codes", Arrays.asList(shopCodes.toString().split(",")));
			sqlWhere.append(" AND dp_shop_code IN(:shop_codes)");
		}
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sqlWhere.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sqlWhere.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sqlWhere.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sqlWhere.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sqlWhere.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(pd_no)){
			if(exactQuery != null && exactQuery.equals(1)){
				sqlWhere.append(" AND pd_no = :pd_no");
			}else {
				sqlWhere.append(" AND INSTR(pd_no,:pd_no) > 0");
			}
		}
		if(StringUtil.isNotEmpty(pd_name)){
			sqlWhere.append(" AND INSTR(pd_name,:pd_name) > 0");
		}
		sqlWhere.append(" AND t.companyid = :companyid");
		return sqlWhere.toString();
	}
	
	@Override
	public Integer count_byshop(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockByShop(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<StockReportByShopDto> list_byshop(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		String type = StringUtil.trimString(params.get("type"));
		sql.append(" SELECT code,name,");
		sql.append(" GROUP_CONCAT(CONCAT(dp_shop_code,':',amount,'_',money)) AS amountmoney");
		sql.append(" FROM");
		sql.append(" (SELECT ");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name,");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name,");
		}
		sql.append(" dp_shop_code,");
		sql.append(" SUM(sd_amount) AS amount,");
		sql.append(" SUM(sd_amount*pd_cost_price) AS money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfStockByShop(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code,dp_shop_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code,dp_shop_code");
		}
		sql.append(" )temp");
		sql.append(" GROUP BY code");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY code ASC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockReportByShopDto.class));
	}
	
	private String getWhereSqlOfStockAnalysis(Map<String, Object> params){
		Integer exactQuery = (Integer)params.get("exactQuery");
		StringBuffer sqlWhere = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sqlWhere.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sqlWhere.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("depot_code"))){
			sqlWhere.append(" AND sd_dp_code = :depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sqlWhere.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sqlWhere.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sqlWhere.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			if(exactQuery != null && exactQuery.equals(1)){
				sqlWhere.append(" AND pd_no = :pd_no");
			}else {
				sqlWhere.append(" AND INSTR(pd_no,:pd_no) > 0");
			}
		}
		if(StringUtil.isNotEmpty(params.get("pd_name"))){
			sqlWhere.append(" AND INSTR(pd_name,:pd_name) > 0");
		}
		sqlWhere.append(" AND t.companyid = :companyid");
		return sqlWhere.toString();
	}
	
	@Override
	public List<StockAnalysisDto> list_analysis(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String type = StringUtil.trimString(params.get("type"));
		sql.append("SELECT ");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name,");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name,");
		}else if("year".equals(type)){
			sql.append(" pd_year AS code,pd_year AS name,");
		}else if("season".equals(type)){
			sql.append(" pd_season AS code,pd_season AS name,");
		}
		sql.append(" SUM(sd_amount) AS amount,");
		sql.append(" SUM(sd_amount * pd_cost_price) AS cost_money,");
		sql.append(" SUM(sd_amount * pd_sell_price) AS sell_money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON dp_shop_code = sp_code AND dp.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSqlOfStockAnalysis(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("year".equals(type)){
			sql.append(" GROUP BY pd_year");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY amount DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockAnalysisDto.class));
	}
	
	@Override
	public List<StockShopDto> stock_shop(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		
		sql.append(" SELECT sp_code AS shop_code,sp_name AS shop_name,SUM(sd_amount) AS amount,");
		sql.append(" SUM(sd_amount * pd_cost_price) AS cost_money,");
		sql.append(" SUM(sd_amount * pd_sell_price) AS sell_money,");
		sql.append(" SUM(sd_amount * pd_sort_price) AS sort_money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		String shopCodes = StringUtil.trimString(params.get("shopCodes"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(shopCodes)){
			params.put("shop_codes", Arrays.asList(shopCodes.toString().split(",")));
			sql.append(" AND dp_shop_code IN(:shop_codes)");
		}
		if(StringUtil.isNotEmpty(params.get("depot_code"))){
			sql.append(" AND sd_dp_code = :depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sp_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockShopDto.class));
	}
	
	@Override
	public List<Map<String, Object>> type_level_stock(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_tp_code AS tp_code,dp_shop_code,");
		sql.append(" SUM(sd_amount) AS amount,");
		sql.append(" SUM(sd_amount * pd_cost_price) AS money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String shopCodes = StringUtil.trimString(params.get("shopCodes"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(shopCodes)){
			params.put("shop_codes", Arrays.asList(shopCodes.toString().split(",")));
			sql.append(" AND dp_shop_code IN(:shop_codes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY pd_tp_code,dp_shop_code");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params);
	}
	
	@Override
	public Integer data_list_saleCount(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object isGift = param.get("isGift");
		Object pd_no = param.get("pd_no");
		Object pd_name = param.get("pd_name");
		Object pd_year = param.get("pd_year");
		Object pd_season = param.get("pd_season");
		Object pd_tp_code = param.get("pd_tp_code");
		Object pd_bd_code = param.get("pd_bd_code");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM(");
		sql.append(" SELECT 1");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_dp_code IN(:allDepot)");
		
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (instr(pd.pd_name,:searchContent)>0 OR instr(pd.pd_spell,:searchContent)>0 OR pd.pd_code=:searchContent OR pd.pd_no=:searchContent)");
        }
        if(null != isGift && !"".equals(isGift)){
			sql.append(" AND pd.pd_gift=:isGift");
		}
        if(null != pd_no && !"".equals(pd_no)){
			sql.append(" AND pd.pd_no=:pd_no");
		}
		if(null != pd_name && !"".equals(pd_name)){
			sql.append(" AND instr(pd.pd_name,:pd_name)>0");
		}
		if(null != pd_year && !"".equals(pd_year)){
			sql.append(" AND pd.pd_year=:pd_year");
		}
		if(null != pd_season && !"".equals(pd_season)){
			sql.append(" AND pd.pd_season=:pd_season");
		}
		if (pd_tp_code != null && !"".equals(pd_tp_code) && !"0".equals(pd_tp_code)) {
			pd_tp_code = pd_tp_code.toString().replace(",", "','");
			sql.append(" AND pd.pd_tp_code IN ('"+pd_tp_code+"')");
		}
		if(null != pd_bd_code && !"".equals(pd_bd_code)){
			sql.append(" AND pd.pd_bd_code=:pd_bd_code");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code,sd_cr_code,sd_sz_code,sd_br_code");
		sql.append(" )temp");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Stock_DataView> data_list_sale(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object start = param.get("start");
		Object end = param.get("end");
		Object isGift = param.get("isGift");
		Object pd_no = param.get("pd_no");
		Object pd_name = param.get("pd_name");
		Object pd_year = param.get("pd_year");
		Object pd_season = param.get("pd_season");
		Object pd_tp_code = param.get("pd_tp_code");
		Object pd_bd_code = param.get("pd_bd_code");
        
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sd_id,sd_pd_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_dp_code,SUM(sd_amount) AS sd_amount,SUM(sd_init) AS sd_init,");
		sql.append(" pd_no,pd_name,pd_bd_code,pd_tp_code,pd_date,pd_year,pd_season,pd_style,pd_fabric,pd_unit,pd_cost_price,pd_sell_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sd_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sd_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sd_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_dp_code IN(:allDepot)");
		if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (instr(pd.pd_name,:searchContent)>0 OR instr(pd.pd_spell,:searchContent)>0 OR pd.pd_code=:searchContent OR pd.pd_no=:searchContent)");
        }
		if(null != isGift && !"".equals(isGift)){
			sql.append(" AND pd.pd_gift=:isGift");
		}
        if(null != pd_no && !"".equals(pd_no)){
			sql.append(" AND pd.pd_no=:pd_no");
		}
		if(null != pd_name && !"".equals(pd_name)){
			sql.append(" AND instr(pd.pd_name,:pd_name)>0");
		}
		if(null != pd_year && !"".equals(pd_year)){
			sql.append(" AND pd.pd_year=:pd_year");
		}
		if(null != pd_season && !"".equals(pd_season)){
			sql.append(" AND pd.pd_season=:pd_season");
		}
		if (pd_tp_code != null && !"".equals(pd_tp_code) && !"0".equals(pd_tp_code)) {
			pd_tp_code = pd_tp_code.toString().replace(",", "','");
			sql.append(" AND pd.pd_tp_code IN ('"+pd_tp_code+"')");
		}
		if(null != pd_bd_code && !"".equals(pd_bd_code)){
			sql.append(" AND pd.pd_bd_code=:pd_bd_code");
		}
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sd_pd_code,sd_cr_code,sd_sz_code,sd_br_code");
		sql.append(" ORDER BY sd_pd_code ASC");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Stock_DataView> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Stock_DataView.class));
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public NumberForm otherCount(Map<String, Object> param) {
		String pd_no = (String)param.get("pd_no");
		String sz_code = (String)param.get("sz_code");
		List<String> dp_codes = (List<String>)param.get("dp_codes");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(1) as code,Sum(number) as number FROM ");
		sql.append(" (SELECT SUM(sd_amount) as number");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(null != dp_codes && dp_codes.size() > 0){
			sql.append(" AND t.sd_dp_code IN (:dp_codes)");
		}
		if(!StringUtil.isEmpty(sz_code)){
			sql.append(" AND t.sd_sz_code=:sz_code");
		}
		if(!StringUtil.isEmpty(pd_no)){
			sql.append(" AND INSTR(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_code ) tb");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param,new BeanPropertyRowMapper<>(NumberForm.class));
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T_Stock_DataView> otherList(Map<String, Object> param) {
		String pd_no = (String)param.get("pd_no");
		String sz_code = (String)param.get("sz_code");
		String br_code = (String)param.get("br_code");
		List<String> dp_codes = (List<String>)param.get("dp_codes");
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sd_code,pd_no,pd_name,sd_cr_code,sd_sz_code,sd_br_code,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.sd_cr_code AND c.companyid=t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT br_name FROM t_base_bra b WHERE b.br_code=t.sd_br_code AND b.companyid=t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT sz_name FROM t_base_size s WHERE s.sz_code=t.sd_sz_code AND s.companyid=t.companyid LIMIT 1) AS sz_name,");
		sql.append(" SUM(sd_amount) AS sd_amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(null != dp_codes && dp_codes.size() > 0){
			sql.append(" AND t.sd_dp_code IN (:dp_codes)");
		}
		if(!StringUtil.isEmpty(br_code)){
			sql.append(" AND t.sd_br_code=:br_code");
		}
		if(!StringUtil.isEmpty(sz_code)){
			sql.append(" AND t.sd_sz_code=:sz_code");
		}
		if(!StringUtil.isEmpty(pd_no)){
			sql.append(" AND INSTR(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_code");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sd_code ASC");
		}
		sql.append(" LIMIT :start,:end");
		List<T_Stock_DataView> list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Stock_DataView.class));
		return list;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<T_Stock_DataView> allOtherList(Map<String, Object> param) {
		String pd_no = (String)param.get("pd_no");
		String sz_code = (String)param.get("sz_code");
		String br_code = (String)param.get("br_code");
		List<String> dp_codes = (List<String>)param.get("dp_codes");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sd_code,sd_dp_code,sd_amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(null != dp_codes && dp_codes.size() > 0){
			sql.append(" AND t.sd_dp_code IN (:dp_codes)");
		}
		if(!StringUtil.isEmpty(br_code)){
			sql.append(" AND t.sd_br_code=:br_code");
		}

		if(!StringUtil.isEmpty(sz_code)){
			sql.append(" AND t.sd_sz_code=:sz_code");
		}
		if(!StringUtil.isEmpty(pd_no)){
			sql.append(" AND INSTR(p.pd_no,:pd_no)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_dp_code,sd_code");
		List<T_Stock_DataView> allList = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Stock_DataView.class));
		return allList;
	}
	@Override
	public List<T_Stock_Data> listAPI(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT dp_code FROM t_base_depot where dp_shop_code=:shop_code AND dp_default=1 and companyid=:companyid limit 1");
		String dp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, String.class);
		param.put("dp_code", dp_code);
		sql.setLength(0);
		sql.append("SELECT sd_id,sd_pd_code,SUM(sd_amount) AS sd_amount,pd_no,pd_name,pd_szg_code,pd_sell_price AS pd_price");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND sd_dp_code=:dp_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Stock_Data.class));
	}
	@Override
	public T_Stock_Data loadByBarcodeAPI(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT dp_code FROM t_base_depot where dp_shop_code=:shop_code AND dp_default=1 and companyid=:companyid limit 1");
		String dp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, String.class);
		param.put("dp_code", dp_code);
		sql.setLength(0);
		sql.append("SELECT sd_id,sd_pd_code,SUM(sd_amount) AS sd_amount,pd_no,pd_name,pd_szg_code,pd_sell_price AS pd_price");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" JOIN t_base_barcode bc ON bc_pd_code = pd.pd_code AND bc.companyid = pd.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND bc_barcode = :barcode");
		sql.append(" AND sd_dp_code=:dp_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param,
					new BeanPropertyRowMapper<>(T_Stock_Data.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public List<T_Base_Size> querySizeByPdCode(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT s.sz_code,sz_name ");
		sql.append(" FROM t_base_sizelist t");
		sql.append(" JOIN t_base_size s");
		sql.append(" ON s.sz_code=t.szl_sz_code");
		sql.append(" AND s.companyid=t.companyid");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON t.szl_szg_code=p.pd_szg_code");
		sql.append(" AND t.companyid=p.companyid");
		sql.append(" WHERE p.pd_code=:pd_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Base_Size.class));
	}

	@Override
	public List<T_Stock_DataView> queryByPdCode(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT pd_no,pd_name,pd_sell_price,sd_dp_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,SUM(sd_amount) AS sd_amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_depot d");
		sql.append(" ON d.dp_code=t.sd_dp_code");
		sql.append(" AND d.companyid=t.companyid");
		sql.append(" AND d.dp_default=1");
		sql.append(" AND d.dp_shop_code=:shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND t.sd_pd_code=:pd_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_code");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Stock_DataView.class));
	}

	@Override
	public List<T_Stock_DataView> otherAPI(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dp_name,sd_amount ");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot d");
		sql.append(" ON d.dp_code=t.sd_dp_code");
		sql.append(" AND d.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=d.dp_shop_code");
		sql.append(" AND s.companyid=d.companyid");
		sql.append(" AND s.sp_upcode=:shop_upcode");
		sql.append(" JOIN common_type y");
		sql.append(" ON y.ty_id=s.sp_shop_type");
		sql.append(" AND y.ty_one=1");
		sql.append(" WHERE t.sd_code=:sub_code");
		sql.append(" AND t.sd_dp_code<>:dp_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Stock_DataView.class));
	}

	@Override
	public Map<String, Object> getCheckImportStock(T_Sys_User user,String depot_code) {
		Integer companyid = user.getCompanyid();
		Map<String, Object> checkImportStock = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("");
		//查询尺码组对应尺码
		List<T_Base_SizeList> sizeLists = new ArrayList<T_Base_SizeList>();
		sql.append(" SELECT szl_szg_code,szl_sz_code,sz_name ");
		sql.append(" FROM t_base_sizelist szl");
		sql.append(" JOIN t_base_size sz ");
		sql.append(" ON sz.sz_code=szl.szl_sz_code AND sz.companyid=szl.companyid");
		sql.append(" WHERE szl.companyid=:companyid ");
		sql.append(" ORDER BY szl_szg_code ");
		sizeLists = namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_SizeList.class));
		Map<String,Object> sizeGroupMap=new HashMap<String, Object>();
		if(sizeLists != null && sizeLists.size()>0){
			for(int i=0;i<sizeLists.size();i++){
				Map<String,String> sizeMap=null;
				String szg_code = sizeLists.get(i).getSzl_szg_code();
				if (sizeGroupMap.get(szg_code)==null){
					sizeMap=new HashMap<String, String>();
					sizeGroupMap.put(szg_code,new HashMap<String, String>());
				}
				sizeMap=(Map<String,String>)sizeGroupMap.get(szg_code);
				sizeMap.put(sizeLists.get(i).getSz_name(), sizeLists.get(i).getSzl_sz_code());
			}
		}
		
		//查询商品货号
		List<T_Base_Product> products = new ArrayList<T_Base_Product>();
		sql.setLength(0);
		sql.append("SELECT pd_code,pd_no,pd_szg_code FROM t_base_product WHERE 1=1 ");
		sql.append(" AND companyid = :companyid ");
		products = namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Product.class));
		Map<String,Map<String,Object>> productMap = new HashMap<String, Map<String,Object>>();
		if(products != null && products.size()>0){
			for(int i=0;i<products.size();i++){
				Map<String,Object> info=new HashMap<String,Object>();
				info.put("pdCode", products.get(i).getPd_code());
				info.put("brMap",new HashMap<String,String>());
				info.put("colorMap",new HashMap<String,String>());
				String szg_code=products.get(i).getPd_szg_code();
				if (sizeGroupMap.get(szg_code)!=null){
					Map<String,String> sizeMap=(Map<String,String>)sizeGroupMap.get(szg_code);
					info.put("sizeMap",sizeMap);	
				}else{
					info.put("sizeMap",new HashMap<String,String>());	
				}
				productMap.put(products.get(i).getPd_no().toLowerCase(),info);	
			}
		}
		
		//查询杯型
		List<T_Base_Product_Br> product_brs = new ArrayList<T_Base_Product_Br>();
		sql.setLength(0);
		sql.append(" select pdb_pd_code,pd_no,pdb_br_code,br_name");
		sql.append(" from t_base_product_br pdb ");
		sql.append(" join t_base_bra br ");
		sql.append(" on br.br_code=pdb.pdb_br_code and br.companyid=pdb.companyid ");
		sql.append(" join t_base_product pd ");
		sql.append(" on pd.pd_code=pdb.pdb_pd_code and pd.companyid=pdb.companyid ");
		sql.append(" where pdb.companyid=:companyid");
		sql.append(" order by pdb_pd_code");
		product_brs = namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Product_Br.class));
		if(product_brs != null && product_brs.size()>0){
			for(int i=0;i<product_brs.size();i++){
				String pd_no = product_brs.get(i).getPd_no().toLowerCase();
				Map<String,Object> infoMap=productMap.get(pd_no);
				if(infoMap!=null){
					Map<String,String> brMap=(Map<String,String>)infoMap.get("brMap");
					if(brMap!=null){
						brMap.put(product_brs.get(i).getBr_name(), product_brs.get(i).getPdb_br_code());
					}
				}
			}
		}
		
		//查询颜色
		List<T_Base_Product_Color> product_colors = new ArrayList<T_Base_Product_Color>();
		sql.setLength(0);
		sql.append(" select pdc_pd_code,pd_no,pdc_cr_code,cr_name ");
		sql.append(" from t_base_product_color pdc");
		sql.append(" join t_base_color cr ");
		sql.append(" on cr.cr_code=pdc.pdc_cr_code and cr.companyid=pdc.companyid ");
		sql.append(" join t_base_product pd ");
		sql.append(" on pd.pd_code=pdc.pdc_pd_code and pd.companyid=pdc.companyid ");
		sql.append(" where pdc.companyid = :companyid ");
		sql.append(" order by pdc_pd_code");
		product_colors = namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Product_Color.class));
		if(product_colors != null && product_colors.size()>0){
			for(int i=0;i<product_colors.size();i++){
				String pd_no = product_colors.get(i).getPd_no().toLowerCase();
				Map<String,Object> infoMap=productMap.get(pd_no);
				if(infoMap!=null){
					Map<String,String> colorMap=(Map<String,String>)infoMap.get("colorMap");
					if(colorMap!=null){
						colorMap.put(product_colors.get(i).getCr_name(), product_colors.get(i).getPdc_cr_code());
					}
				}
			}
		}
		checkImportStock.put("productMap", productMap);
			
		//查询库存
		List<T_Stock_Data> stockDatas = new ArrayList<T_Stock_Data>();
		sql.setLength(0);
		sql.append("SELECT sd_pd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_amount,sd_init FROM t_stock_data WHERE 1=1 ");
		sql.append(" AND companyid = :companyid ");
		sql.append(" AND sd_dp_code = "+depot_code);
		stockDatas = namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_Data.class));
		checkImportStock.put("stockList", stockDatas);
		
		return checkImportStock;
	}

	@Override
	public void update_import_stock(List<T_Stock_Data> stockList) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_data");
		sql.append(" SET sd_amount = sd_amount+:sd_amount,");
		sql.append(" sd_init = sd_init+:sd_init ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid = :companyid");
		sql.append(" AND sd_dp_code = :sd_dp_code");
		sql.append(" AND sd_code = :sd_code");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(stockList.toArray()));
	}

	@Override
	public void insert_import_stock(List<T_Stock_Data> stockList) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_data");
		sql.append(" (sd_pd_code,sd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_dp_code,sd_amount,sd_init,sd_date,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sd_pd_code,:sd_code,:sd_cr_code,:sd_sz_code,:sd_br_code,:sd_dp_code,:sd_amount,:sd_amount,:sd_date,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(stockList.toArray()));
	}

	@Override
	public List<T_Stock_Data> listByPdCode(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object sh_shop_code = paramMap.get("sh_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(sd_amount) sd_amount,sd_pd_code");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot dp"); 
		sql.append(" ON sd_dp_code=dp_code");
		sql.append(" AND dp.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON dp_shop_code=s.sp_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			sql.append(" AND sp_code=:sh_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE sd_pd_code IN (:pd_codes)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Stock_Data.class));
	}
	
	public List<T_Stock_Data> listBySubCode(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object shl_shop_code = paramMap.get("shl_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(sd_amount) sd_amount,sd_code,sd_date,sp_code shop_code");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot dp"); 
		sql.append(" ON sd_dp_code=dp_code");
		sql.append(" AND dp.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON dp_shop_code=s.sp_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE sd_code IN (:sub_codes)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sd_code,sp_code");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Stock_Data.class));
	}

	@Override
	public List<T_Stock_Data> upListByCode(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(sd_amount) sd_amount,sd_code,MIN(sd_date) sd_date");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot d");
		sql.append(" ON d.dp_code=t.sd_dp_code");
		sql.append(" AND d.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=d.dp_shop_code");
		sql.append(" AND s.companyid=d.companyid");
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(" AND s.sp_shop_type=:shop_type");
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_code IN (:sub_codes)");
		sql.append(" AND t.companyid=100");
		sql.append(" GROUP BY sd_code");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Stock_Data.class));
	}
	
	@Override
	public List<ProductSingleTrackDto> single_track(Map<String, Object> params) {
		return namedParameterJdbcTemplate.query(SingleTrackSQL.getSingleTrackSQL(params), params, new BeanPropertyRowMapper<>(ProductSingleTrackDto.class));
	}
	
	@Override
	public Map<String, Object> countsum_single_track(Map<String, Object> params) {
		return namedParameterJdbcTemplate.queryForMap(SingleTrackSQL.getSingleTrackCountSumSQL(params), params);
	}
}
